let bodyParser = require('body-parser')
let urlencodedParser = bodyParser.urlencoded({
  extended: false
})
let express = require('express')
let db = require('./db.js')
let router = express.Router()

router.get('/get', async function (req, res) {
  let sql = `select * from classify`
  const { data } = await db.doSQL(sql)
  res.send(db.handleResults(data))
})

router.post('/post', urlencodedParser, async function (req, res) {
  let { name } = req.body
  let sql = `insert into classify (name) values (?)`
  // let sql = `insert into classify (name) values (${name})`

  const data = await Promise.all([db.doSQL(sql, [name])])
  // const data = await Promise.all([db.doSQL(sql)])
  res.send(db.handleResults(data))
})

router.get('/list', async function (req, res) {
  var {
    currentPage = 1,//当前页
    pageSize = 10, //每页多少
    searchVal,
    sort,//正序 or 倒序
    orderby,//通过方式
  } = req.query;

  // 查数据
  var sql = `select m.id,m.title,m.author,m.cid,m.time,m.date,c.name as type from msbd as m,classify as c where m.cid=c.id`
  // 查总数
  var sql_count = "select count(*) as total from msbd as m,classify as c where m.cid=c.id";

  var num = currentPage * pageSize;//总条数
  if (searchVal) {
    sql_count += `  and title like '%${searchVal}%'`;
    sql += `  and m.title like '%${searchVal}%'`;
  }
  if (orderby) {
    sql += `  order by ${orderby} ${sort}`;
  }

  sql += `  limit ${num},${pageSize}`
  sql_classify = ' select * from classify'
  const data = await Promise.all([db.doSQL(sql), db.doSQL(sql_count), db.doSQL(sql_classify)])
  res.send(db.handleResults(data[0].data, data[1].data[0].total, data[2].data))
})

module.exports = router;